package com.lyc.dao.imp;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lyc.dao.MedalInfoDAO;
import com.lyc.dto.MedalDto;
import com.lyc.dto.MedalSearch;
import com.lyc.entity.MedalInfo;
import com.lyc.util.DBUtil;

/**
 * @Program: medal
 * @Author: YC
 * @Date: 2022/5/5 17:42
 */

public class MedalInfoDAOImpl extends DBUtil.BaseDAOImpl<MedalInfo> implements MedalInfoDAO {
	@Override
	public List<MedalDto> selectMap(int index, int len) throws SQLException {
		String sql = "SELECT\n" + " a.*,\n" + " b.sportsman_name,\n" + " b.sportsman_uri,\n" + " c.country_code,\n"
				+ " c.country_uri,\n" + " c.country_name,\n" + " d.se_name\n" + "FROM medal_info a\n"
				+ "INNER JOIN sportsman_info b\n" + "ON a.sportsman_id=b.sportsman_id\n" + "INNER JOIN country_info c\n"
				+ "ON b.country_code=c.country_code\n" + "INNER JOIN sporting_events d\n"
				+ "ON d.se_id = a.se_id order by a.medal_date desc,a.medal_id desc limit ?,?";
		return DBUtil.selectList(sql, MedalDto.class, index, len);
	}

	@Override
	public List<MedalDto> selectSearch(MedalSearch search) throws SQLException {
		List<Object> ps = new ArrayList<>();
		String where = where(search, ps);

		String sql = "SELECT\n" + " a.*,\n" + " b.sportsman_name,\n" + " b.sportsman_uri,\n" + " c.country_code,\n"
				+ " c.country_uri,\n" + " c.country_name,\n" + " d.se_name\n" + "FROM medal_info a\n"
				+ "INNER JOIN sportsman_info b\n" + "ON a.sportsman_id=b.sportsman_id\n" + "INNER JOIN country_info c\n"
				+ "ON b.country_code=c.country_code\n" + "INNER JOIN sporting_events d\n" + "ON d.se_id = a.se_id "
				+ where + " order by a.medal_date desc,a.medal_num,a.medal_id desc limit ?,?";

		ps.add((search.getCurrent() - 1) * search.getRowMax());
		ps.add(search.getRowMax());
		System.out.println(sql);
		System.out.println(ps);
		return DBUtil.selectList(sql, MedalDto.class, ps.toArray());
	}

	@Override
	public long countSearch(MedalSearch search) throws SQLException {
		List<Object> ps = new ArrayList<>();
		String where = where(search, ps);

		String sql = "SELECT count(*) as c " + "FROM medal_info a\n" + "INNER JOIN sportsman_info b\n"
				+ "ON a.sportsman_id=b.sportsman_id\n" + "INNER JOIN country_info c\n"
				+ "ON b.country_code=c.country_code\n" + "INNER JOIN sporting_events d\n" + "ON d.se_id = a.se_id "
				+ where;
		return DBUtil.selectBigDecimal(sql, ps.toArray()).longValue();

	}

	private String where(MedalSearch search, List<Object> list) {
		StringBuilder sb = new StringBuilder();
		if (search.getKey() != null && !"".equals(search.getKey())) {
			sb.append(" AND b.sportsman_name LIKE CONCAT('%',?,'%') ");
			list.add(search.getKey());
		}
		if (search.getCountryCode() != null && !"".equals(search.getCountryCode())) {
			sb.append(" and c.country_code=? ");
			list.add(search.getCountryCode());
		}
		if (search.getMedalNum() != null) {
			sb.append(" and a.medal_num=? ");
			list.add(search.getMedalNum());
		}
		if (search.getSeId() != null) {
			sb.append(" and d.se_id=? ");
			list.add(search.getSeId());
		}
		if (search.getSportsmanId() != null) {
			sb.append(" and b.sportsman_id=? ");
			list.add(search.getSportsmanId());
		}
		return sb.toString();
	}
}
